Before computers, numerical analyses, particularly financial ones, were usually prepared on an accountant's columnar pad or spreadsheet, with pencil and calculator in hand. By organising data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is laborious. The process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Since all calculations are the responsibility of the preparer, manually prepared spreadsheets are also prone to errors. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.
With the advent of microcomputers, a solution was forthcoming in the form of “electronic spreadsheets.” Better known simply as “spreadsheets,” these software programs provide a computerised replacement for the traditional financial modelling tools: the accountant's columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools as word processors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.
A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant's columnar pad, thus providing a visible calculator. Because this “pad” exists dynamically in the computer's memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, a letter to each column, and another letter to each sheet (or page) of the spreadsheet. To reference a location at column A and row 1 of the second page (i.e., the upper-left hand corner), for example, the user types in “B:A1”. In this manner, the spreadsheet defines an addressable storage location or “cell” at each intersection of a row with a column within a given page.
Data entry into an electronic spreadsheet occurs in much the same manner that information would be entered on an accountant's pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or “formulas” specifying calculations to be performed on the numbers stored in spreadsheet cells. Such spreadsheet cells can also be defined and named as a range, as long as they are arranged as a contiguous set of cells. A typical example of such a named range simply corresponds to a regular table found in an accountant's pad. In this fashion, range names can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells. The structure and operation of a spreadsheet program, including advanced functions such as functions and macros, are documented in the technical, trade, and patent literature.
Electronic spreadsheets offer many advantages over their paper counterparts. For one, electronic spreadsheets are much larger (i.e., hold more information) than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet programs also allow users to perform “what-if” scenarios. After a set of computational relationships has been entered into a worksheet, by using imbedded formulas for instance, the spread of information may be recalculated using different sets of assumptions, with the results of each recalculation appearing almost instantaneously. Performing this operation manually, with paper and pencil, would require recalculating every relationship in the model with each change made. Thus, electronic spreadsheet systems were invented to solve “what-if” problems, that is, changing an input and seeing what happens to an output.
For this purpose, electronic spreadsheets systems include different means helping the user both to modify inputs and to visualize the resulting effect.
In the former case, user defined push-buttons with associated software or pieces of code are typical examples of built-in tools available within conventional electronic spreadsheets for manipulating individual cells or ranges of cells.
In the later case, user defined charts are typical examples of built-in tools available within conventional electronic spreadsheets for seeing the resulting effect of an input change. Indeed a chart makes relationships among numbers easy to see because it turns numbers into shapes (lines, bars, slices of a pie), and the shapes can then be compared with one another.
The ranges of cells, whether they include one or several cells, typically constitute the basic objects handled by and associated with both push-buttons and charts. In the following, such ranges of cells will be referred to as “working ranges”. Objects like push-buttons and charts can be “fastened” to the range of cells they overlay on the spreadsheet user computer display. In the following, such ranges will be referred to as the “fastening ranges”.
When a fastening range is copied/cut and pasted within a conventional electronic spreadsheet, the objects that are included are also copied/cut and pasted, so that the resulting pasted range contains the same objects as the original fastening range. Therefore, the pasted range is also a fastening range. Any object within the pasted fastening range owns the same attributes as the corresponding original object does. This does not normally present a problem for most of the object attributes (for instance, a pie chart is expected to be copied/cut and pasted into a pie chart, not a bar chart).
Nevertheless, some limitations exist in conventional electronic spreadsheets for the working range attribute. Indeed, with conventional electronic spreadsheets, the working ranges associated with a copied/cut and pasted object are exactly the same as the ones associated with the original object. This is not the normally expected result if the working range is included in, or equal to, the fastening range: in this case, the working range is expected to be treated as a regular relative range, so that the copied/cut and pasted working range occupies within the copied/cut and pasted fastening range the same relative position as the original working range within the original fastening range.
As the spreadsheet user inappropriately expects that the copied/cut and pasted working range is included in the copied/cut and pasted fastening range, he or she may erroneously interpret the result of a change within the copied/cut and pasted working range. This problem is particularly severe in the case of a cut and paste operation, as the cut and pasted objects within a cut and pasted fastening range are associated with working ranges which have been cut and which are thus emptied. In the case of a chart object, the resulting shape (line, bar, slice of a pie) is simply absent. The present invention offers a user-friendly solution to this problem by giving to the electronic spreadsheet user the choice between a conventional copy/cut and paste operation and an enhanced copy/cut and paste operation where working ranges within fastening ranges are treated as relative ranges.